﻿using System;
using System.Linq;

namespace EntityFrameWorkLearning
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var oAWEntities = new AdventureWorksEntities())
            {
                // 1. Easy example but not very flexible
                //    Select all products without any constraints
                foreach (var product in oAWEntities.Product)
                {
                    Console.WriteLine(string.Format("{0} | {1} | {2}",
                        product.ProductID, product.Name, product.ListPrice));
                }
                Console.ReadLine();

                // 2. Same example as above using LINQ
                foreach (var product in from p in oAWEntities.Product
                                        select p)
                {
                    Console.WriteLine(string.Format("{0} | {1} | {2}",
                        product.ProductID, product.Name, product.ListPrice));
                }
                Console.ReadLine();

                // 3. Same example as above externalizing the LINQ query
                //    Select 20 products without any constraints
                var query = from p in oAWEntities.Product
                            select p;

                foreach (var product in query.Take(20))
                {
                    Console.WriteLine(string.Format("{0} | {1} | {2}",
                        product.ProductID, product.Name, product.ListPrice));
                }
                Console.ReadLine();

                // 4. Example using Lambda Expressions
                //    Select 20 products that have a review rating above 3
                var queryLambda = from p in oAWEntities.Product
                            where p.ProductReview.Any(pr => pr.Rating > 3)
                            select p;

                foreach (var product in queryLambda.Take(20))
                {
                    Console.WriteLine(string.Format("{0} | {1} | {2}",
                        product.ProductID, product.Name, product.ListPrice));
                }

                Console.ReadLine();
                Console.Clear();

                // 5. This serves to see what SQL queries are send to the Database
                var oQuery = (System.Data.Objects.ObjectQuery<Product>)query;
                Console.WriteLine(oQuery.ToTraceString());
                Console.ReadLine();

                using (var oAWEntities2 = new AdventureWorksEntities())
                {
                    // 6. Select the count of all products that are sold out
                    //    This will only work if you re-add the deleted MakeFlag
                    //var querySoldOut = from p in oAWEntities2.Product
                    //                   where p.MakeFlag == true
                    //                   select p;
                    //Console.WriteLine(string.Format("SoldOutProductCount: {0}", querySoldOut.Count()));
                    //Console.ReadLine();

                    // 7. Select the count of all products that are sold out
                    var querySoldOut = from p in oAWEntities2.Product
                                       where p is SoldOutProduct
                                       select p;

                    Console.WriteLine(string.Format("SoldOutProductCount: {0}", querySoldOut.Count()));
                    Console.ReadLine();
                }

                // 8. Update the StandardCost value
                using (var oAWEntities4 = new AdventureWorksEntities())
                {
                    var queryForUpdateCost = from p in oAWEntities4.Product
                                where p.ProductID.Equals(3)
                                select p;

                    var product = queryForUpdateCost.First();

                    Console.WriteLine(string.Format("{0} | {1} | {2}",
                        product.Name, product.ProductNumber, product.StandardCost));
                    Console.ReadLine();

                    product.StandardCost += 100;
                    oAWEntities4.SaveChanges();
                }

                // 9. Query the StandardCost value to verify update
                using (var oAWEntities5 = new AdventureWorksEntities())
                {
                    var queryForUpdateCost2 = from p in oAWEntities5.Product
                                where p.ProductID.Equals(3)
                                select p;

                    var product = queryForUpdateCost2.First();

                    Console.WriteLine(string.Format("{0} | {1} | {2}",
                        product.Name, product.ProductNumber, product.StandardCost));
                    Console.ReadLine();
                }

                // 10. Try to set the StandardCost value to a negative value --> get exception
                using (var oAWEntities6 = new AdventureWorksEntities())
                {
                    var queryForUpdateCost3 = from p in oAWEntities6.Product
                                where p.ProductID.Equals(3)
                                select p;

                    var product = queryForUpdateCost3.First();

                    product.StandardCost = -10;
                    oAWEntities6.SaveChanges();
                }
            }            
        }
    }
}
